USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_occurs') BEGIN
	DROP  FUNCTION  tsql.fn_occurs
	PRINT '<<< DROP FUNCTION tsql.fn_occurs - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			tsql.fn_occurs
Type:			User defined function 
Owner:					
Description:	Counts the number of occurences of a string whitin an other
Parameters:		@string = string to search in for substring occurences
				@search_for = substring to search for occurences
Dependens on:  	none
Usage:			SELECT [Lib].[tsql].[fn_occurs] ('123455','5')
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_occurs&referringTitle=Documentation		
Version:		20120822
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		12/02/2005 - Adrian E Dudau - Function created
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/

CREATE FUNCTION tsql.fn_occurs 
	(@string AS NVARCHAR(MAX)
	,@search_for NVARCHAR(MAX))
RETURNS BIGINT 
AS 
	BEGIN 
		-- Since LEN excludes trailing spaces
		-- space is replaced with non printable character
		SET @string = REPLACE(@string,CHAR(32),CHAR(255))
		SET @search_for = REPLACE(@search_for,CHAR(32),CHAR(255))

		-- Count no of occurences
		RETURN (LEN(@string) - LEN(REPLACE(@string, @search_for,'')))/ISNULL(NULLIF(LEN(@search_for),0),1)
	END
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE type = 'FN' AND name = 'fn_occurs') BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_occurs - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION tsql.fn_occurs - Completed with ERROR >>>'
END


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

--  GRANT SELECT TO PUBLIC will be used at Db level
